import sqlite3
import json
import os
import time


class MyDatabases:
    def __init__(self):
        self.conn = sqlite3.connect('./u_disk_use_info.db')
        self.cursor = self.conn.cursor()
        self.create_table()
        self.init_tables()
       
    def check_login(self, username, passwd):
        query_users = "select user_name,password from user_info where user_name = 'admin';"
        users = self.search_data(query_users)
        for user in users:
            user_name,password = user
            if username == user_name:
                if passwd == password:
                    return True, '登录成功！'
                return False, '登录失败，密码不正确！'
        return False, '登录失败，用户不存在！'
    
    def update_data(self):
        pass
    
    def delete_data(self):
        pass
    
    def insert_data(self,sql,data=None):
        self.cursor.execute(sql,data)
        self.committ()
        
    def search_data(self,sql):
        query_obj = self.cursor.execute(sql)
        return query_obj.fetchall()
    
    def create_table(self):
        try:
            users = ['id integer PRIMARY KEY autoincrement','user_name TEXT', 'password TEXT']
            inventories = ['id integer PRIMARY KEY autoincrement',
                           'U_type TEXT', 'U_number TEXT unique', 'U_status TEXT','use_status text']
            lend_out = ['id integer PRIMARY KEY autoincrement',
                           'used TEXT', 'U_number TEXT', 'U_type TEXT','use_date text']
            returned = ['id integer PRIMARY KEY autoincrement',
                           'used TEXT', 'U_number TEXT', 'U_type TEXT','returner text','use_date text','ret_date text']
            user_fields = ",".join([field for field in users])
            inven_fields = ",".join([inven for inven in inventories])
            lend_fields = ",".join([lend for lend in lend_out])
            ret_fields = ",".join([ret for ret in returned])
            sql1 = f"CREATE TABLE IF NOT EXISTS user_info ({user_fields});"
            sql2 = f"CREATE TABLE IF NOT EXISTS inventory ({inven_fields});"
            sql3 = f"CREATE TABLE IF NOT EXISTS lended ({lend_fields});"
            sql4 = f"CREATE TABLE IF NOT EXISTS rets ({ret_fields});"
            for sql in [sql1, sql2, sql3, sql4]:
                self.cursor.execute(sql)
            self.committ()
            return True, '创建表成功'
        except Exception as e:
            return False, e
        
    def init_tables(self):
        selected = "select 'admin' from user_info where user_name = 'admin';"
        users = self.search_data(selected)
        if not users:
            sql = "insert into user_info(user_name, password) values(?,?);"
            self.insert_data(sql, ('admin', '123456'))
            
    def committ(self):
        self.conn.commit()
    
    def closecursor(self):
        self.cursor.close()
        
    def disconnnect(self):
        self.conn.close()


db = MyDatabases()
